package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vcp.entity.CEOWorker;
import vcp.entity.Complaint;
import vcp.entity.CustomerServiceWorker;
import vcp.entity.Site;
import vcp.entity.SiteCostUpdates;
import vcp.entity.SiteManagerWorker;
import vcp.entity.SiteWorker;
import vcp.entity.User;
import vcp.entity.Worker;
import vcp.enums.Departments;
import vcp.enums.JobType;
import vcp.enums.UserType;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.Constants.UserTable;
import vcp.server.utility.Constants.WorkerTable;
import vcp.server.utility.SQLQueries;

/**
 * 
 * @author VCP team
 *         <p>
 *         worker DAO class all worker relevant sql queries are implemented
 *         </p>
 */
public class WorkerDAO implements IGenericDAO <Worker, User>
{
	// ****************************************************************************************************************************
	// Public methods
	// ****************************************************************************************************************************
	/**
	 * create new instance according to job type
	 * 
	 * @param job
	 * @return instance of worker
	 */
	public Worker create ( JobType job )
	{
		Worker worker = null;
		switch ( job )
		{
			case CEO:
				worker = new CEOWorker();
				break;
			case CUSTOMER_SERVICE:
				worker = new CustomerServiceWorker();
				break;
			case SITE_MANAGER:
				worker = new SiteManagerWorker();
				break;
			case SITE_WORKER:
				worker = new SiteWorker();
				break;
		}

		return worker;
	}

	/**
	 * 
	 * @return all workers found in workers table in DB
	 */
	public List <Worker> findAllWorkers ()
	{
		List <Worker> workers = new ArrayList <>();

		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM_SINGLETABLE
					, TableNames.WORKER);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			UserDAO userDAO = new UserDAO();
			while ( set.next() )
			{
				JobType workerType = JobType.convert(set.getString(WorkerTable.JOB_TYPE));
				Worker worker = this.create(workerType);
				worker.setJobType(workerType);
				worker.setDepartment(Departments.convert(set.getString(WorkerTable.DEPARTMENT_NAME)));
				worker.setWorkerID(set.getInt(WorkerTable.ID));

				User workerAsUser = userDAO.findByID(worker.getWorkerID());
				worker.setUserID(workerAsUser.getUserID());
				worker.setPassword(workerAsUser.getPassword());
				worker.setUserFirstName(workerAsUser.getUserFirstName());
				worker.setUserLastName(workerAsUser.getUserLastName());
				worker.setUserType(workerAsUser.getUserType());
				worker.setUserEmail(workerAsUser.getUserEmail());

				Integer siteID = set.getInt(WorkerTable.SITE_ID);
				List <Site> workerSites = new ArrayList <>();
				SiteDAO siteDAO = new SiteDAO();
				if ( ( siteID == null ) || ( siteID == 0 ) )
				{
					workerSites = siteDAO.findAll(null);
				}
				else
				{
					workerSites.add(siteDAO.findByID(siteID));
				}

				if ( workerType == JobType.CUSTOMER_SERVICE )
				{
					ComplaintDAO complaintDAO = new ComplaintDAO();
					List <Complaint> complaints = complaintDAO.findAllComplaints();
					worker.setComplaints(complaints);
				}

				if ( worker.getDepartment() == Departments.MANAGEMENT )
				{
					SiteCostUpdatesDAO siteCostUpdatesDAO = new SiteCostUpdatesDAO();
					List <SiteCostUpdates> listOfUpdates =
							worker.getJobType() == JobType.SITE_MANAGER
																		? siteCostUpdatesDAO.findAllNonFinishedSiteCostUpdates(workerSites.get(0).getSite())
																		: siteCostUpdatesDAO.findAllPendingSiteCostUpdates();
					worker.setSiteCostUpdates(listOfUpdates);
				}

				worker.setSites(workerSites);
				workers.add(worker);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return workers;
	}

	// ****************************************************************************************************************************
	// IGenericDAO methods
	// ****************************************************************************************************************************
	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	/**
	 * Not Implemented
	 */
	@Override
	public Worker create ()
	{
		return null;
	}

	@Override
	public Worker findByEntity ( User entity )
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_, TableNames.WORKER, WorkerTable.ID + "=" + String.valueOf(entity.getUserID()));

		Worker worker = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				JobType workerType = JobType.convert(set.getString(WorkerTable.JOB_TYPE));
				worker = this.create(workerType);

				worker.setUserID(entity.getUserID());
				worker.setPassword(entity.getPassword());
				worker.setUserFirstName(entity.getUserFirstName());
				worker.setUserLastName(entity.getUserLastName());
				worker.setUserEmail(entity.getUserEmail());
				worker.setUserType(entity.getUserType());

				Integer siteID = set.getInt(WorkerTable.SITE_ID);
				worker.setJobType(workerType);
				worker.setWorkerID(set.getInt(WorkerTable.ID));
				worker.setDepartment(Departments.convert(set.getString(WorkerTable.DEPARTMENT_NAME)));

				List <Site> workerSites = new ArrayList <>();
				SiteDAO siteDAO = new SiteDAO();
				if ( ( siteID == null ) || ( siteID == 0 ) )
				{
					workerSites = siteDAO.findAll(null);
				}
				else
				{
					workerSites.add(siteDAO.findByID(siteID));
				}

				if ( workerType == JobType.CUSTOMER_SERVICE )
				{
					ComplaintDAO complaintDAO = new ComplaintDAO();
					List <Complaint> complaints = complaintDAO.findAllComplaints();
					worker.setComplaints(complaints);
				}

				if ( worker.getDepartment() == Departments.MANAGEMENT )
				{
					SiteCostUpdatesDAO siteCostUpdatesDAO = new SiteCostUpdatesDAO();
					List <SiteCostUpdates> listOfUpdates =
							worker.getJobType() == JobType.SITE_MANAGER
																		? siteCostUpdatesDAO.findAllNonFinishedSiteCostUpdates(workerSites.get(0).getSite())
																		: siteCostUpdatesDAO.findAllPendingSiteCostUpdates();
					worker.setSiteCostUpdates(listOfUpdates);
				}

				worker.setSites(workerSites);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return worker;
	}

	@Override
	public Worker findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Worker findByID ( String ID )
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__TABLES__WHERE__COMPARE_, TableNames.USER + "," + TableNames.WORKER, WorkerTable.ID + "=" + ID);

		Worker worker = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				JobType workerType = JobType.convert(set.getString(WorkerTable.JOB_TYPE));
				worker = this.create(workerType);

				worker.setUserID(set.getInt(UserTable.ID));
				worker.setPassword(set.getString(UserTable.PASSWORD));
				worker.setUserFirstName(set.getString(UserTable.FIRST_NAME));
				worker.setUserLastName(set.getString(UserTable.LAST_NAME));
				worker.setUserType(UserType.convert(set.getString(UserTable.TYPE)));
				worker.setUserEmail(set.getString(UserTable.EMAIL));

				Integer siteID = set.getInt(WorkerTable.SITE_ID);
				worker.setJobType(workerType);
				worker.setDepartment(Departments.convert(set.getString(WorkerTable.DEPARTMENT_NAME)));
				worker.setWorkerID(set.getInt(WorkerTable.ID));

				List <Site> workerSites = new ArrayList <>();
				SiteDAO siteDAO = new SiteDAO();
				if ( ( siteID == null ) || ( siteID == 0 ) )
				{
					workerSites = siteDAO.findAll(null);
				}
				else
				{
					workerSites.add(siteDAO.findByID(siteID));
				}

				if ( workerType == JobType.CUSTOMER_SERVICE )
				{
					ComplaintDAO complaintDAO = new ComplaintDAO();
					List <Complaint> complaints = complaintDAO.findAllComplaints();
					worker.setComplaints(complaints);
				}

				if ( worker.getDepartment() == Departments.MANAGEMENT )
				{
					SiteCostUpdatesDAO siteCostUpdatesDAO = new SiteCostUpdatesDAO();
					List <SiteCostUpdates> listOfUpdates =
							worker.getJobType() == JobType.SITE_MANAGER
																		? siteCostUpdatesDAO.findAllNonFinishedSiteCostUpdates(workerSites.get(0).getSite())
																		: siteCostUpdatesDAO.findAllPendingSiteCostUpdates();
					worker.setSiteCostUpdates(listOfUpdates);
				}

			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return worker;
	}

	/**
	 * Not implemented
	 */
	@Override
	public List <Worker> findAll ( User info )
	{
		return null;
	}

	/**
	 * Not Implemented
	 */
	@Override
	public boolean save ( Worker entity )
	{
		return false;
	}

	/**
	 * Not Implemented
	 */
	@Override
	public boolean refresh ( String sqlQuery )
	{
		return false;
	}

	/**
	 * Not Implemented
	 */
	@Override
	public boolean deleteByID ( int ID )
	{
		return false;
	}

	/**
	 * Not Implemented
	 */
	@Override
	public boolean deleteByID ( String ID )
	{
		return false;
	}
}
